package com.softer.chengxin.dongle.dao.provider;

import com.softer.chengxin.common.domin.Page;
import org.apache.commons.lang3.StringUtils;

import java.util.Map;

/**
 * @author Gong Zhiyang
 * @date 2018/9/26
 */
public class DongleDaoProvider {

    /**
     * 获取加密狗设备列表
     *
     * @return 结果集
     */
    public String loadDongleInfo(Map<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        sql.append("select s.`name` as registerAddress,d.id AS id,d.key_code AS keyCode ,DATE_FORMAT(d.allot_date, '%Y-%m-%d') AS allotDate,m.name AS alloter,d.state AS state ");
        sql.append(" from dong_leinfo d LEFT JOIN company_base_info c on d.corp_id = c.id ");
        sql.append(" LEFT JOIN district_street s ON s.id = c.district ");
        sql.append(" LEFT JOIN manager_user m ON d.allo_ter= m.login_name ");
        sql.append(" where d.corp_id=#{id} and d.iszy=1");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE c.community WHEN 0 THEN c.street in (").append(area).append(") ELSE c.community in (").append(area).append(") end)");
        }
        return sql.toString();
    }


    /**
     * 查询企业的项目加密狗数量
     *
     * @return sql
     */
    public String countQyDongleNum(Map<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        sql.append("SELECT COUNT(1) FROM company_project b LEFT JOIN (SELECT project_id FROM dong_leinfo WHERE iszy=0 )a");
        sql.append(" ON a.project_id = b.id LEFT JOIN district_street s ON s.id = b.district WHERE b.company_id =  #{id}");
//        sql.append("SELECT count(*) FROM dong_leinfo a LEFT JOIN company_project b ON a.project_id = b.id");
//        sql.append(" WHERE a.corp_id = #{id} AND a.iszy=0");
        return sql.toString();
    }

    /**
     * 查询企业的项目加密狗列表
     *
     * @return sql
     */
    public String countQyDongleList(Map<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        sql.append("SELECT b.id, DATE_FORMAT(a.allot_date, '%Y-%m-%d') as allotDate,a.allo_ter as alloter,");
        sql.append(" a.key_code as keyCode,b.project_name as projectName,s.name as registerAddress,a.state ");
        sql.append(" FROM company_project b LEFT JOIN (SELECT project_id,allot_date,allo_ter,key_code,state");
        sql.append(" FROM dong_leinfo WHERE iszy=0 )a  ON a.project_id = b.id");
        sql.append(" LEFT JOIN district_street s ON s.id = b.district WHERE b.company_id = #{id}");
//        sql.append("SELECT a.id, DATE_FORMAT(a.allot_date, '%Y-%m-%d') as allotDate,a.allo_ter as alloter,a.key_code as keyCode,");
//        sql.append("b.project_name as projectName,s.name as registerAddress,a.state ");
//        sql.append(" FROM dong_leinfo a LEFT JOIN company_project b ON a.project_id = b.id");
//        sql.append(" LEFT JOIN district_street s ON s.id = b.district ");
//        sql.append(" WHERE a.corp_id = #{id} AND a.iszy=0");
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 主管部门获取加密狗企业数量
     *
     * @param map 参数
     * @return sql
     */
    public String loadCorpNum(Map<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String name = (String) map.get("name");
        String state = (String) map.get("state");
        String area = (String) map.get("area");
        sql.append("SELECT count(*) FROM company_base_info ");
        sql.append(" WHERE (CASE community WHEN 0 THEN street in (").append(area).append(") ");
        sql.append(" ELSE community in (").append(area).append(") end) ");
        commonSql(sql, name, state);
        return sql.toString();
    }

    /**
     * 主管部门获取加密狗企业列表
     *
     * @param map 参数
     * @return sql
     */
    public String loadCorpList(Map<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        String name = (String) map.get("name");
        String state = (String) map.get("state");
        String area = (String) map.get("area");
        sql.append("SELECT id,qy_no as qyNo,`name`,register_address as registerAddress ,(CASE state WHEN 2 then 1 ELSE 2 END) as state ");
        sql.append(" FROM company_base_info WHERE (CASE community WHEN 0 THEN street in (").append(area).append(") ");
        sql.append(" ELSE community in (").append(area).append(") end) ");
        commonSql(sql, name, state);
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    private void commonSql(StringBuffer sql, String name, String state) {
        sql.append(" and state in (").append(state).append(")");
        if (StringUtils.isNotBlank(name)) {
            sql.append(" and instr(name,#{name})>0");
        }
    }


}
